﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gedea.Model.Entities;
using System.Data;
using System.Data.SqlClient;
using Gedea.Model;
using Gedea.Core.Manager;
using Gedea.Core.Manager.DataAccess;

namespace Gedea.Core.Repositories
{
    public class AreaRepository
    {
        public void Add(Area area)
        {
            using (SqlConnection conn = new SqlConnection(DataConnection.GetConnection()))
            {
                string statement =
                    string.Format("INSERT INTO Areas ({0}, {1}, {2}) VALUES (?, ?, ?)"
                    , DBObjects.Areas.name.ToString()
                    , DBObjects.Areas.debitUnivocalReference.ToString()
                    , DBObjects.Areas.userId.ToString());

                SqlCommand command = new SqlCommand(statement, conn);

                command.Parameters.Add(DBObjects.Areas.name.ToString(), SqlDbType.VarChar).Value = area.Name;
                command.Parameters.Add(DBObjects.Areas.debitUnivocalReference.ToString(), SqlDbType.VarChar).Value = area.DebitUnivocalReference;
                command.Parameters.Add(DBObjects.Areas.userId.ToString(), SqlDbType.Decimal).Value = area.UserId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        public Area Get(int id)
        {
            return new DataLookFor().Get<Area>();
        }

        public void Update(Area area)
        {
            using (SqlConnection conn = new SqlConnection(DataConnection.GetConnection()))
            {
                string statement =
                    string.Format("UPDATE Areas SET {0} = ?, {1} = ?, {2} = ? WHERE {3} = ?;"
                    , DBObjects.Areas.name.ToString()
                    , DBObjects.Areas.debitUnivocalReference.ToString()
                    , DBObjects.Areas.userId.ToString()
                    , DBObjects.Areas.areaId.ToString());

                SqlCommand command = new SqlCommand(statement, conn);

                command.Parameters.Add(DBObjects.Areas.name.ToString(), SqlDbType.VarChar).Value = area.Name;
                command.Parameters.Add(DBObjects.Areas.debitUnivocalReference.ToString(), SqlDbType.VarChar).Value = area.DebitUnivocalReference;
                command.Parameters.Add(DBObjects.Areas.userId.ToString(), SqlDbType.Decimal).Value = area.UserId;
                command.Parameters.Add(DBObjects.Areas.areaId.ToString(), SqlDbType.Decimal).Value = area.AreaId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            } 
        }

        public List<Area> List(int userId)
        {
            string query =
                string.Format("SELECT * FROM Areas WHERE {0} = {1} ORDER BY {2}"
                , DBObjects.Areas.userId.ToString()
                , userId
                , DBObjects.Areas.name.ToString());

            return new DataLookFor().ListEntity<Area>(query);
        }

        private Area ConvertToArea(IDataReader dr)
        {
            return new DataLookFor().MapToEntity<Area>(dr, false);
        }
    }
}
